﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using TugasAkhir.Helpers;
using TugasAkhir.Objects;

namespace TugasAkhir.Models
{
    public static class AnalisisModel
    {
        public static bool GetTahun(out DataSet hasilQuery)
        {
            string query = "SELECT DISTINCT [tahun] FROM [dbo].[detail_matakuliah]";
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetPeriode(string tahun, out DataSet hasilQuery)
        {
            string query = String.Format("USE [TA] SELECT DISTINCT [periode] FROM [dbo].[detail_pertanyaan] WHERE [tahun] = '{0}'", tahun);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetMatakuliah(string tahun, string periode, string idMahasiswa, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DISTINCT DP.[id_matakuliah], DP.[kelas], M.[nama_matakuliah] FROM [dbo].[detail_pertanyaan] DP, [dbo].[matakuliah] M WHERE DP.[id_matakuliah] = M.[id_matakuliah] AND DP.[tahun] = '{0}' AND DP.[periode] = '{1}' AND DP.[status_pengisian] = '0' AND DP.[id_mahasiswa] = '{2}'", tahun, periode, idMahasiswa);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetMatakuliah(string tahun, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DM.[id_matakuliah], M.[nama_matakuliah] FROM [dbo].[detail_matakuliah] DM, [dbo].[matakuliah] M WHERE DM.[id_matakuliah] = M.[id_matakuliah] AND DM.[tahun] = '{0}'", tahun);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetPertanyaan(string tahun, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT A.[id_pertanyaan], P.[pertanyaan] FROM [dbo].[analisis] A, [dbo].[pertanyaan] P WHERE A.[id_pertanyaan] = P.[id_pertanyaan] AND A.[tahun] = '{0}'", tahun);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetPertanyaan(string tahun, string idMatakuliah, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT A.[id_pertanyaan], P.[pertanyaan] FROM [dbo].[analisis] A, [dbo].[pertanyaan] P WHERE A.[id_pertanyaan] = P.[id_pertanyaan] AND A.[id_matakuliah] = '{0}' AND A.[tahun] = '{1}'", idMatakuliah, tahun);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetMean(string tahun, string periode, string idMatakuliah, out DataSet hasilQuery)
        {
            string tempTahun1 = String.Format("{0}-{1}", int.Parse(tahun.Substring(5)), int.Parse(tahun.Substring(5)) + 1);
            string tempTahun2 = String.Format("{0}-{1}", int.Parse(tahun.Substring(5)) + 1, int.Parse(tahun.Substring(5)) + 2);

            string query = String.Format("SELECT [tahun], AVG([nilai_akhir]) AS mean FROM [dbo].[pengambilan_mahasiswa] WHERE ([tahun] = '{0}' OR [tahun] = '{1}' OR [tahun] = '{2}') AND [periode] = '{3}' AND [id_matakuliah] = '{4}' GROUP BY [tahun]", tahun, tempTahun1, tempTahun2, periode, idMatakuliah);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetPengaruhSkor(string idMatakuliah, List<string> tahun, string periode, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DISTINCT A.[id_pertanyaan], A.[nilai_pengaruh], A.[skor], A.[tahun], CONVERT(VARCHAR(MAX), P.[pertanyaan]) AS [pertanyaan] FROM [dbo].[analisis] A, [dbo].[pertanyaan] P WHERE A.[id_pertanyaan] = P.[id_pertanyaan] AND A.[id_matakuliah] = '{0}' AND {1} AND A.[periode] = '{2}' ORDER BY A.[id_pertanyaan] ASC, A.[tahun] ASC", idMatakuliah, PrintTahun(tahun), periode);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        private static string PrintTahun(List<string> tahun)
        {
            string result = "";
            if (tahun.Count == 1)
            {
                result += String.Format("A.[tahun] = '{0}'", tahun[0]);
            }
            else
            {
                result += "(";
                int count = tahun.Count - 1;
                foreach (string s in tahun)
                {
                    result += String.Format("A.[tahun] = '{0}'", s);
                    if (count > 0)
                    {
                        result += " OR ";
                        count--;
                    }
                }
                result += ")";
            }

            return result;
        }

        public static bool Perbarui(AnalisisObject analisis)
        {
            string query = String.Format("UPDATE [dbo].[analisis] SET [nilai_validitas] = {0} ,[nilai_reliabilitas] = {1} ,[nilai_pengaruh] = {2} ,[skor] = {3} WHERE [tahun] = '{4}' AND [id_matakuliah] = '{5}' AND [id_pertanyaan] = '{6}'", analisis.Validitas, analisis.Reliabilitas, analisis.Pengaruh, analisis.Skor, analisis.Tahun, analisis.Matakuliah, analisis.Pertanyaan);
            return SqlServer.ExecuteNonQuery(query);
        }

        public static bool GetTahunAnalisis(out DataSet hasilQuery)
        {
            string query = "SELECT DISTINCT([tahun]) FROM [dbo].[analisis]";
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetMatakuliahAnalisis(string tahun, string periode, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DISTINCT A.[id_matakuliah], M.[nama_matakuliah] FROM [dbo].[analisis] A, [dbo].[matakuliah] M WHERE A.[id_matakuliah] = M.[id_matakuliah] AND A.[tahun] = '{0}' AND A.[periode] = '{1}'", tahun, periode);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetMatakuliahAnalisis(string tahun, string periode, string idDosen, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DISTINCT A.[id_matakuliah], M.[nama_matakuliah] FROM [dbo].[analisis] A, [dbo].[matakuliah] M, [dbo].[pengambilan_dosen] PD WHERE A.[id_matakuliah] = M.[id_matakuliah] AND A.[id_matakuliah] = PD.[id_matakuliah] AND A.[periode] = PD.[periode] AND A.[tahun] = PD.[tahun] AND A.[tahun] = '{0}' AND A.[periode] = '{1}' AND PD.[id_dosen] = '{2}'", tahun, periode, idDosen);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetPeriodeAnalisis(string tahun, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DISTINCT [periode] FROM [dbo].[analisis] WHERE [tahun] = '{0}'", tahun);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetPertanyaanAnalisis(string tahun, string matakuliah, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DISTINCT A.[id_pertanyaan], CONVERT(VARCHAR(MAX), P.[pertanyaan]) AS pertanyaan FROM [dbo].[analisis] A, [dbo].[pertanyaan] P WHERE A.[id_pertanyaan] = P.[id_pertanyaan] AND A.[tahun] = '{0}' AND A.[id_matakuliah] = '{1}'", tahun, matakuliah);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetRekomendasiAnalisis(string tahun, string periode, string matakuliah, out DataSet hasilQuery)
        {
            string query = String.Format(" SELECT A.[id_rekomendasi], A.[id_pertanyaan], P.[pertanyaan], R.[rekomendasi] FROM [dbo].[analisis] A, [dbo].[rekomendasi] R, [dbo].[pertanyaan] P WHERE A.[id_rekomendasi] = R.[id_rekomendasi] AND A.[id_pertanyaan] = P.[id_pertanyaan] AND A.[tahun] = '{0}' AND A.[id_matakuliah] = '{2}' AND A.[periode] = '{1}'", tahun, periode, matakuliah);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetRekomendasi(string idPertanyaan, string tahun, string periode, string idMatakuliah, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT A.[id_rekomendasi], R.[rekomendasi] FROM [dbo].[analisis] A, [dbo].[rekomendasi] R WHERE A.[id_rekomendasi] = R.[id_rekomendasi] AND A.[tahun] = '{0}' AND A.[periode] = '{1}' AND A.[id_matakuliah] = '{2}' AND A.[id_pertanyaan] = '{3}'", tahun, periode, idMatakuliah, idPertanyaan);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool Tambah(AnalisisObject analisis)
        {
            string query = String.Format("INSERT INTO [dbo].[analisis] VALUES ('{0}' ,'{1}' ,'{2}' ,'{3}' ,'{4}' ,'{5}' ,'{6}' ,'{7}' ,'{8}')", analisis.Periode, analisis.Tahun, analisis.Matakuliah, analisis.Rekomendasi, analisis.Pertanyaan, analisis.Validitas, analisis.Reliabilitas, analisis.Pengaruh, analisis.Skor);
            return SqlServer.ExecuteNonQuery(query);
        }

        public static bool Hapus(AnalisisObject analisis)
        {
            string query = String.Format("DELETE FROM [dbo].[analisis] WHERE [periode] = '{0}' AND [tahun] = '{1}' AND [id_matakuliah] = '{2}' AND [id_rekomendasi] = '{3}' AND [id_pertanyaan] = '{4}'", analisis.Periode, analisis.Tahun, analisis.Matakuliah, analisis.Rekomendasi, analisis.Pertanyaan);
            return SqlServer.ExecuteNonQuery(query);
        }
    }
}